<?php
/**
 * Query2 is a minimalist MySQL layer which can get maximum of MySQL database in minimal code
 *
 * @author Adam Zivner <adam.zivner@gmail.com>
 * @license http://www.opensource.org/licenses/bsd-license.php New BSD license
 * @package Query2
 * @link http://query2.0o.cz
 */

defined("QUERY2_VERSION") || define("QUERY2_VERSION", "1.1.4");

class Query2 {
	/** @var resource - MySQL connection */
	public $con; // MySQL connection

	/** @var callback - see setLogCallback() */
	protected $log_callback = null;

	/**
	 * Create object and optionally connect do database server and select database.
	 * If no arguments are given, we don't try to connect, if $database is empty, we
	 * don't select database. See also connect() method for details.
	 *
	 * @param string $host
	 * @param string $login
	 * @param string $password
	 * @param string $database
	 * @param boolean $new_link - enforce creation of the new link
	 * @param integer $client_flags - see mysql_connect() documentation
	 *
	 * @throws Query2Exception - codes 100 (can't connect to server), 101 (can't select database)
	 */

	public function __construct($host = '', $login = '', $password = '', $database = '', $new_link = false, $client_flags = 0)
	{
		if(func_num_args() > 0)
			$this->connect($host, $login, $password, $database, $new_link, $client_flags);
	}

	public function connect($host, $login, $password, $database = '', $new_link = false, $client_flags = 0)
	{
		if(!$this->con = mysql_connect($host, $login, $password, $new_link, $client_flags))
			throw new Query2Exception(100, "Can't connect to database server.");
		else if(strlen($database) && !mysql_select_db($database, $this->con))
			throw new Query2Exception(101, "Can't select database.");

		return $this;
	}

	public function close()
	{
		if($this->con)
			mysql_close($this->con);

		return $this;
	}

	/**
	 * Callback is called right after execution of every query (before error exception may be raised).
	 * Function should expect 3 arguments: success (bool), query (string), elapsed time in seconds
	 *
	 * @param callback $callback
	 */

	public function setLogCallback($callback) { $this->log_callback = $callback; return $this; }
	public function getLogCallback() { return $this->log_callback; }

	// Transaction handling routines

	public function beginTransaction()
	{
		$this->query("SET AUTOCOMMIT=0");
		$this->query("START TRANSACTION");

		return $this;
	}

	public function commit()
	{
		$this->query("COMMIT");
		$this->query("SET AUTOCOMMIT=1");

		return $this;
	}

	public function rollBack()
	{
		$this->query("ROLLBACK");
		$this->query("SET AUTOCOMMIT=1");

		return $this;
	}

	public function affectedRows() { return mysql_affected_rows($this->con); }
	public function lastInsertId() { return mysql_insert_id($this->con); }

	/**
	 * Compose SQL query from given arguments. Apply modifiers etc.
	 * Used by query and pquery.
	 *
	 * @throws Query2Exception - codes 200 (wrong argument list/order) and 201 (unknown modifier)
	 * @return string - final, ready to be executed SQL query
	 */

	public function composeQuery()
	{
		return $this->composeQueryFromArray(func_get_args());
	}

	protected function composeQueryFromArray($arr)
	{
		$args = array(); // SQL chunks/arguments
		$first = $arr[0]; // first argument

		// if first argument is array, take it as a list of arguments
		foreach(is_array($first) ? $first : $arr as $arg)
			if(is_object($arg) && method_exists($arg, 'mergeQuery')) // is this Query2Builder?
				$args = array_merge($args, $arg->mergeQuery());
			else
				$args[] = $arg;

		$sql = "";

		// Now, let's deal with modifiers

		for($i = 0, $argc = count($args); $i < $argc; $i++) {
			$chunk = $args[$i];

			if(!is_string($chunk))
				throw new Query2Exception(200, 'Wrong argument/modifier order to query() function: ' . var_export($args, true));

			// find modifiers
			preg_match_all("/%(?:%|([a-zA-Z]*)(?![a-zA-Z]))/", $chunk, $matches, PREG_SET_ORDER);

			foreach($matches as $m) {
				if($m[0] == '%%')
					$arg = '%';
				else {
					$arg = $args[++$i];

					if($arg === null)
						$arg = 'NULL';
					else if($m[1] == 'i') // integer
						$arg = (int) $arg;
					else if($m[1] == 'f') // float
						$arg = (float) $arg;
					else if(strtolower($m[1]) == 's') // string
						$arg = "'" . ($m[1] == 's' ? $this->escape($arg) : $arg) . "'";
					else if(strtolower($m[1]) == 't') // table or column name
						$arg = $this->escapeTableName($arg, $m[1] == 't');
					else if(strtolower($m[1]) == 'x') // insert pure SQL query, no enclosing, optionally escaping
						$arg = $m[1] == 'x' ? $this->escape($arg) : $arg;
					else if(strtolower($m[1]) == 'in' || strtolower($m[1]) == 'nin') // inserts IN ('a', 'b', 'c')
						$arg = $this->in($arg, strtolower($m[1]) == 'in', $m[1] == 'in' || $m[1] == 'nin');
					else if(strtolower($m[1]) == 'a') // update modifier, returns name='John', surname='Black'
						$arg = $this->assocUpdate($arg, $m[1] == 'a');
					else if(strtolower($m[1]) == 'v') // insert modifier, returns (name, surname) VALUES ('John', 'Black')
						$arg = $this->assocInsert($arg, $m[1] == 'v');
					else if(strtolower($m[1]) == 'va') // INSERT ... ON DUPLICATE KEY UPDATE
						$arg = $this->assocInsertUpdate($arg, $m[1] == 'va');
					else
						throw new Query2Exception(201, "Unknown modifier '$m[1]' to query() function.");
				}

				$chunk = $this->str_replace_once($m[0], $arg, $chunk);
			}

			$sql .= $chunk . ' ';
		}

		return trim($sql);
	}

	/**
	 * Main Query function. Query is composed by helper function composeQuery()
	 *
	 * Arguments can be:
	 * - chunks of SQL (with modifiers)
	 * - modifier arguments
	 * - instances of Query2Builder
	 * - an array as first argument containing anything from this list
	 *
	 * @throws Query2Exception - same as exec()
	 * @return Query2Result|Query2 - Query2Result for SELECT, DESCRIBE etc. and Query2 for INSERT, UPDATE etc.
	 */

	public function query() { return $this->exec(func_get_args(), "mysql_query"); }

	/**
	 * Executes unbuffered query, otherwise identical to query()
	 *
	 * @throws Query2Exception - same as exec()
	 * @return Query2Result|Query2 - Query2Result for SELECT, DESCRIBE etc. and Query2 for INSERT, UPDATE etc.
	 */

	public function uquery() { return $this->exec(func_get_args(), "mysql_unbuffered_query"); }

	/**
	 * Inner query execution method.
	 *
	 * @param array $args - array of args given to query() or uquery()
	 * @param string $query_function - either "mysql_query" or "mysql_unbuffered_query"
	 *
	 * @throws Query2Exception - code 300 (MySQL error) and the same one as composeQuery()
	 * @return Query2Result|Query2
	 */

	protected function exec($args, $query_function)
	{
		// get pure SQL with composeQueryArray
		$sql = $this->composeQueryFromArray($args);

		$started_time = microtime(true); // we'll measure how much time execution of a query took

		// here we go!
		if($query_function == "mysql_query")
			$res = mysql_query($sql, $this->con);
		else if($query_function == "mysql_unbuffered_query")
			$res = mysql_unbuffered_query($sql, $this->con);
		else
			throw new Query2Exception("Internal error: unknown query function.");

		if($this->log_callback) // calling the callback (useful for logging, performance tuning etc.)
			call_user_func($this->log_callback, !mysql_error($this->con), $sql, microtime(true) - $started_time);

		if(mysql_error($this->con)) // oops, query failed
			throw new Query2Exception(300, mysql_error($this->con), $sql);

		return is_resource($res) ? new Query2Result($res) : $this;
	}

	/**
	 * Prints query and then executes it. It's intended for in-place use instead
	 * of query for debugging, just replace "query(" with "pquery(".
	 *
	 * If you just want to get query string (e.g. to write to file), use composeQuery()
	 *
	 * @throws Query2Exception - same codes as query()
	 * @return Query2Result|Query2
	 */

	public function pquery()
	{
		$args = func_get_args();
		echo $this->composeQueryFromArray($args);
		return call_user_func_array(array($this, "query"), $args);
	}

	/**
	 * Execute multiple queries either from string or stream. It can handle only
	 * the most basic form - it expects that ; as the last nonwhite character on the
	 * line signals the end of the query. It works with exports from phpMyAdmin and similar tools
	 * just fine. This method is different from uquery() and query() methods in that it
	 * doesn't expect modifiers.
	 *
	 * @param string|stream $h - either actual SQL queries or a file handle (stream) in which they are stored
	 * @return integer - number of executed queries
	 */

	public function mquery($h)
	{
		if(!is_resource($h)) // if it's a string create "string resource"
			$h = fopen('data://text/plain,' . $h, 'r');

		$query_count = 0;

		for($line = $sql = ''; $line !== false; $sql .= $line = fgets($h))
			if(substr(rtrim($line), -1) == ';') {
				$this->query("%X", $sql);
				$sql = '';
				$query_count++;
			}

		if(trim($sql) != '') { // last query does not have to be trailed by ;
			$this->query("%X", $sql);
			$query_count++;
		}

		fclose($h);
		return $query_count;
	}

	/** Factory method */
	public function builder() { return new Query2Builder(); }

	/** Factory method */
	public function statement($statement) { return new Query2Statement($statement); }

	/**
	 * Escape schema object names. ` is escaped by doubling it, ' and " are not because the don't have to be escaped.
	 * E.g. table.column => `table`.`column`
	 * 
	 * @param string $str
	 * @param boolean $escape
	 * @return string
	 */

	public function escapeTableName($str, $escape)
	{
		if($escape)
			$str = str_replace("`", "``", $str);

		$arr = explode(".", $str);

		return "`" . implode("`.`", $arr) . "`";
	}

	/**
	 * @param string $str
	 * @return string
	 */

	public function escape($str) { return mysql_real_escape_string($str, $this->con); }

	/**
	 * Escapes value according to its type and $escape
	 *
	 * @param mixed $v
	 * @param boolean $escape
	 * @return string
	 */

	protected function escapeValue($v, $escape)
	{
		if($v === null)
			return "NULL";
		else if(is_object($v) && get_class($v) == "Query2Statement")
			return $v->statement;
		else
			return "'" . ($escape ? $this->escape($v) : $v) . "'";
	}

	/**
	 * Generate (NOT) IN('a', 'b', 'c') from given array. It works also with an empty array too.
	 *
	 * @param array $arr
	 * @param boolean $in - is it IN or NOT IN?
	 * @param boolean $escape
	 * @return string - either "FALSE" or "IN(...)"
	 */

	protected function in($arr, $in, $escape)
	{
		if(count($arr)) {
			foreach($arr as $key => $value)
				$arr[$key] = $this->escapeValue($value, $escape);

			return ($in ? '' : 'NOT ') . 'IN (' . implode(", ", $arr) . ')';
		}
		else
			return $in ? 'AND FALSE' : 'IS NOT NULL';
	}

	/**
	 * Transforms associative array into the UPDATE form
	 * name = 'John', surname = 'Black', age = '33'
	 *
	 * @params array $assoc
	 * @params boolean $escape - escape contents?
	 * @return string
	 */

	protected function assocUpdate($assoc, $escape)
	{
		$arr = array();

		foreach($assoc as $key => $value)
			$arr[] = "`" . $this->escape($key) . "` = " . $this->escapeValue($value, $escape);

		return implode(", ", $arr);
	}

	/**
	 * Transforms associative array into the INSERT INTO form:
	 * (name, surname, age) VALUES ('John', 'Black', '33')
	 *
	 * @params array $assoc
	 * @params boolean $escape - escape contents?
	 * @return string
	 */

	protected function assocInsert($arr, $escape)
	{
		$vars = $ret = array();

		foreach((isset($arr[0]) && is_array($arr[0])) ? $arr : array($arr) as $assoc) {
			$vars = $values = array();

			foreach($assoc as $key => $value) {
				$vars[] = "`" . $this->escape($key) . "`";

				$values[] = $this->escapeValue($value, $escape);
			}

			$ret[] = "(" . implode(", ", $values) . ")";
		}

		return "(" . implode(", ", $vars) . ") VALUES " . implode(", ", $ret);
	}

	/**
	 * This method implements useful INSERT ... ON DUPLICATE KEY UPDATE.
	 * There are two ways to use this function, "simple" and "complex".
	 * See http://query2.0o.cz for explanation.
	 *
	 * @param array $arr - two possibilities - see documentation
	 * @param boolean $escape - escape content?
	 * @return string
	 */

	protected function assocInsertUpdate($arr, $escape)
	{
		$complex = isset($arr["data"]) && is_array($arr["data"]);
		$update = ($complex && isset($arr["update"])) ? $arr["update"] : array_keys($complex ? $arr["data"] : $arr);

		foreach($update as $idx => $name) // we'll do it in-place
			$update[$idx] = "`$name` = VALUES(`$name`)";

		if($complex && isset($arr["auto_increment"]))
			$update[] = "`$arr[auto_increment]` = LAST_INSERT_ID(`$arr[auto_increment]`)";

		return $this->assocInsert($complex ? $arr["data"] : $arr, $escape) . " ON DUPLICATE KEY UPDATE " . implode(", ", $update);
	}

	protected function str_replace_once($needle, $replace, $haystack)
	{
		$pos = strpos($haystack, $needle);

		return $pos === false ? $haystack : substr_replace($haystack, $replace, $pos, strlen($needle));
	}
}

class Query2Exception extends Exception {
	public $code, $error, $sql;

	public function __construct($code, $error, $sql = "")
	{
		$this->code = $code; // error code (e.g. 300)
		$this->error = $error;
		$this->sql = $sql;

		parent::__construct($code . " " . $error . (strlen($sql) ? ": $sql" : ""), $code);
	}
}

/**
 * QueryStatement provides a way to use unescaped value like (MySQL NOW()) in
 * otherwise escaped role (in %v, %V and %a, %A).
 */

class Query2Statement {
	public $statement;

	public function __construct($statement) { $this->statement = $statement; }
}